<?php
/*****************************************************************************
Copyright © 2008 The Regents of the University of Nevada
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
1. Redistributions of source code must retain the above copyright
   notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
   notice, this list of conditions and the following disclaimer in the
   documentation and/or other materials provided with the distribution.
3. The name of the author may not be used to endorse or promote products
   derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*****************************************************************************/

class RDBMSDataStore implements iDataStore {

	private $dbcon, $dbms;
	private static $instance;
	private static $available_dbms = array('mysql', 'pgsql');

	protected function __construct() {}
	public function __clone() {}
	public function __wakeup() {}


	public final function getConnection() {
		if (!$this->dbcon) {
			switch ($this->getDBMS()) {
				case 'mysql':
					$this->dbcon = $this->getMySQLConnection(); break;
				case 'pgsql':
					$this->dbcon = $this->getPgSQLConnection(); break;
			}
		}
		return $this->dbcon;
	} // getConnection()


	public static function getInstance() {
		if (!self::$instance instanceof self) {
			self::$instance = new self;
			switch (Preference::getInstance()->getDBEngine()) {
				case 'mysql':
					self::$instance->setDBMS('mysql'); break;
				case 'pgsql':
					self::$instance->setDBMS('pgsql'); break;
				default:
					throw new DatabaseErrorException(String::UNKNOWN_DBMS
						. implode(', ', self::getAvailableDBMS()));
					break;
			}
		}
		return self::$instance;
	} // getInstance()


	private function getMySQLConnection() {
		if (!function_exists('mysql_connect')) {
			throw new DatabaseErrorException(
				sprintf(String::DBMS_NOT_COMPILED_INTO_PHP, 'MySQL'));
			return false;
		}

		$host = Preference::getInstance()->getDBHost();
		if (strlen(Preference::getInstance()->getDBPort()) > 1) {
			$host .= ':' . Preference::getInstance()->getDBPort();
		}

		if (!$dbcon = @mysql_connect($host,
		Preference::getInstance()->getDBUser(),
		Preference::getInstance()->getDBPass())) {
			throw new DatabaseErrorException(mysql_error());
			return false;
		}
		if (!@mysql_select_db(Preference::getInstance()->getDBName())) {
			throw new DatabaseErrorException(String::UNABLE_TO_SELECT_DATABASE);
			return false;
		}

		return $dbcon;
	} // connectToMySQL()


	public function getName() { return 'RDBMS'; }


	private function getPgSQLConnection() {
		if (!function_exists('pg_connect')) {
			throw new DatabaseErrorException(
				sprintf(String::DBMS_NOT_COMPILED_INTO_PHP, 'PostgreSQL'));
			return false;
		}

		$str[] = 'connect_timeout=10';
		if (strlen(Preference::getInstance()->getDBHost()) > 0)
			$str[] = 'host=' . Preference::getInstance()->getDBHost();
		if (strlen(Preference::getInstance()->getDBUser()) > 0)
			$str[] = 'user=' . Preference::getInstance()->getDBUser();
		if (strlen(Preference::getInstance()->getDBPass()) > 0)
			$str[] = 'password=' . Preference::getInstance()->getDBPass();
		if (strlen(Preference::getInstance()->getDBName()) > 0)
			$str[] = 'dbname=' . Preference::getInstance()->getDBName();
		if (strlen(Preference::getInstance()->getDBPort()) > 0)
			$str[] = 'port=' . Preference::getInstance()->getDBPort();
		if (!$dbcon = @pg_connect(implode(' ', $str))) {
			throw new DatabaseErrorException(
				sprintf(String::DBMS_CONNECT_FAILURE, 'PostgreSQL')
			);
			return false;
		}
		return $dbcon;
	} // connectToPgSQL()


	public function escape($str) {
		switch ($this->getDBMS()) {
			case 'mysql':
				return mysql_real_escape_string($str, $this->getConnection());
				break;
			case 'pgsql':
				return pg_escape_string($this->getConnection(), $str);
				break;
		}
	} // escape()


	public static final function getAvailableDBMS() {
		return self::$available_dbms;
	} // getAvailableDBMS()


	private function getDBMS() {
		return $this->dbms;
	} // getDBMS()


	private function setDBMS($dbms) {
		if (in_array($dbms, self::getAvailableDBMS()))
			$this->dbms = $dbms;
		else return false;
	} // setDBMS()


	public function read($sql) {
		$dbcon = $this->getConnection();
		$data = array();
		switch ($this->getDBMS()) {
			case 'mysql':
				if (!$result = mysql_query($sql, $dbcon)) {
					throw new DatabaseErrorException(
						mysql_error($dbcon), $sql);
					break;
				}
				while ($row = mysql_fetch_assoc($result)) {
					$data[] = $row;
				}
				break;
			case 'pgsql':
				if (!$result = pg_query($dbcon, $sql)) {
					throw new DatabaseErrorException(
						pg_last_error($dbcon), $sql);
					break;
				}
				while ($row = pg_fetch_assoc($result)) {
					$data[] = $row;
				}
				break;
			default:
				throw new InternalErrorException('Unable to find a DBMS from which
					to read.');
				break;
		}
		return $data;
	} // read()


	public function write($sql) {
		$dbcon = $this->getConnection();
		switch ($this->getDBMS()) {
			case 'mysql':
				if (!$result = mysql_query($sql, $dbcon)) {
					throw new DatabaseErrorException(
						mysql_error($dbcon), $sql);
				}
				break;
			case 'pgsql':
				if (!$result = pg_query($dbcon, $sql)) {
					throw new DatabaseErrorException(
						pg_last_error($dbcon), $sql);
				}
				break;
			default:
				throw new InternalErrorException('Unable to find a DBMS to write
					to.');
				break;
		}
		return $result;
	} // write()

	/***************************** COLLECTIONS *******************************/

	public final function getAllCollections() {
		$result = $this->read(Query::ALL_COLLECTIONS_SQL);
		$cols = array();
		foreach ($result as $c) {
			$cols[] = new Collection($c['id']);
		}
		return $cols;
	}


	public final function getAllCollectionsAsArray() {
		return $this->read(Query::ALL_COLLECTIONS_SQL);
	}


	public final function getAllCollectionsOutside($lat_n, $lat_s, $long_e,
	$long_w) {
		$sql = sprintf(Query::ALL_COLLECTIONS_OUTSIDE_SQL,
			$this->escape($lat_n), $this->escape($lat_s),
			$this->escape($long_e), $this->escape($long_w)
		);
		return $this->getCollectionObjectsFromSQL($sql);
	}


	public final function getAllCollectionsPartiallyWithin($lat_n, $lat_s,
	$long_e, $long_w) {
		$sql = sprintf(Query::ALL_COLLECTIONS_PARTIALLY_WITHIN_SQL,
			$this->escape($long_w), $this->escape($long_e),
			$this->escape($long_e), $this->escape($long_w),
			$this->escape($lat_s), $this->escape($lat_n),
			$this->escape($lat_n), $this->escape($lat_s)
		);
		return $this->getCollectionObjectsFromSQL($sql);
	}


	public final function getAllCollectionsWithin($lat_n, $lat_s, $long_e,
	$long_w) {
		$sql = sprintf(Query::ALL_COLLECTIONS_WITHIN_SQL,
			$this->escape($lat_s), $this->escape($long_w),
			$this->escape($lat_n), $this->escape($long_e)
		);
		return $this->getCollectionObjectsFromSQL($sql);
	}


	public final function deleteCollection(Collection $c) {
		$sql = sprintf(Query::DELETE_COLLECTION_SQL,
			$this->escape($c->getID())
		);
		return $this->write($sql);
	}


	private function getCollectionObjectsFromSQL($sql) {
		$cols = array();
		foreach ($this->read($sql) as $r) {
			$cols[] = new Collection($r['id']);
		}
		return $cols;
	}


	public final function loadCollectionProperties(Collection $c) {
		$sql = sprintf(Query::GET_COLLECTION_PROPERTIES_SQL,
			$this->escape($c->getID()));
		foreach ($this->read($sql) as $r) {
			$c->setAlias($r['alias']);
			$c->setBaseURL($r['base_url']);
			$c->setIntroURL($r['intro_url']);
			$c->setName($r['name']);
			$c->setOAIIDPrefix($r['oai_id_prefix']);
			$c->setOrganization($r['organization']);
			$c->setOrganizationURL($r['org_url']);
			$c->setPathToOAI($r['path_to_oai']);
			$c->setPathToThumbnail($r['path_to_thumbnail']);
		}
	}


	public final function saveCollection(Collection $c) {
		if (!$this->collectionIsInDataStore($c)) { // insert
			$sql = sprintf(Query::ADD_COLLECTION_SQL,
				$this->escape($c->getAlias()), $this->escape($c->getBaseURL()),
				$this->escape($c->getIntroURL()), $this->escape($c->getName()),
				$this->escape($c->getOAIIDPrefix()),
				$this->escape($c->getOrganization()),
				$this->escape($c->getOrganizationURL()),
				$this->escape($c->getPathToOAI()),
				$this->escape($c->getPathToThumbnail())
			);
		}
		else { // update
			$sql = sprintf(Query::UPDATE_COLLECTION_SQL,
				$this->escape($c->getAlias()), $this->escape($c->getBaseURL()),
				$this->escape($c->getIntroURL()), $this->escape($c->getName()),
				$this->escape($c->getOAIIDPrefix()),
				$this->escape($c->getOrganization()),
				$this->escape($c->getOrganizationURL()),
				$this->escape($c->getPathToOAI()),
				$this->escape($c->getPathToThumbnail()), $this->escape($c->getID())
			);
		}
		return $this->write($sql);
	}


	public final function collectionHasSiblingWithSameID(Collection $c) {
		$sql = sprintf(Query::COLLECTION_ID_EXISTS_SQL,
			$this->escape($c->getID()));
		$result = $this->read($sql);
		return ($result[0]['count'] > 0) ? true : false;
	}


	public final function collectionHasSiblingWithSameNameAndURL(Collection $c) {
		$sql = sprintf(Query::COLLECTION_NAME_URL_EXISTS_SQL,
			$this->escape($c->getName()), $this->escape($c->getBaseURL())
		);
		$result = $this->read($sql);
		return ($result[0]['count'] > 0) ? true : false;
	}


	/******************************** MAPS ***********************************/

	public final function getAllMaps($page, $results_per_page, $sort,
	$sort_order) {
		$offset = ($page - 1) * $results_per_page ;
		$sql = sprintf(Query::ALL_MAPS_SQL,
			$this->escape($sort), $this->escape($sort_order),
			$this->escape($results_per_page), $this->escape($offset)
		);
		return $this->getMapObjectsFromSQL($sql);
	}


	public final function getAllMapsAsArray() {
		$sql = sprintf(Query::ALL_MAPS_SQL, 'id', 'asc', 100000, 0);
		return $this->read($sql);
	}


	public final function getAllMapsOutside($lat_n, $lat_s, $long_e, $long_w,
	$page, $results_per_page, $sort, $sort_order, $group_by_collection,
	array $collection_ids) {

		$offset = ($page - 1) * $results_per_page ;
		$sql = ($group_by_collection)
			? Query::ALL_MAPS_OUTSIDE_GROUPED_SQL : Query::ALL_MAPS_OUTSIDE_SQL;

		$cid_stmt = (count($collection_ids) > 0)
			? 'AND collection_id IN(' . implode(', ', $collection_ids) . ')'
			: null;
		$sql = sprintf($sql,
			$this->escape($lat_n), $this->escape($lat_s),
			$this->escape($long_e), $this->escape($long_w),
			$this->escape($cid_stmt),
			$this->escape($sort), $this->escape($sort_order),
			$this->escape($results_per_page), $this->escape($offset)
		);
		return $this->getMapObjectsFromSQL($sql);
	}


	public final function getAllMapsPartiallyWithin($lat_n, $lat_s, $long_e,
	$long_w, $page, $results_per_page, $sort, $sort_order,
	$group_by_collection, array $collection_ids) {

		$offset = ($page - 1) * $results_per_page ;
		$sql = ($group_by_collection)
			? Query::ALL_MAPS_PARTIALLY_WITHIN_GROUPED_SQL
			: Query::ALL_MAPS_PARTIALLY_WITHIN_SQL;

		$cid_stmt = (count($collection_ids) > 0)
			? 'AND collection_id IN(' . implode(', ', $collection_ids) . ')'
			: null;
		$sql = sprintf($sql,
			$this->escape($long_w), $this->escape($long_e), $this->escape($long_e),
			$this->escape($long_w), $this->escape($lat_s), $this->escape($lat_n),
			$this->escape($lat_n), $this->escape($lat_s),
			$this->escape($cid_stmt),
			$this->escape($sort), $this->escape($sort_order),
			$this->escape($results_per_page), $this->escape($offset)
		);
		return $this->getMapObjectsFromSQL($sql);
	}


	public final function getAllMapsWithin($lat_n, $lat_s, $long_e, $long_w,
	$page, $results_per_page, $sort, $sort_order, $group_by_collection,
	array $collection_ids) {

		$offset = ($page - 1) * $results_per_page ;
		$sql = ($group_by_collection)
			? Query::ALL_MAPS_WITHIN_GROUPED_SQL : Query::ALL_MAPS_WITHIN_SQL;
		$cid_stmt = (count($collection_ids) > 0)
			? 'AND collection_id IN(' . implode(', ', $collection_ids) . ')'
			: null;
		$sql = sprintf($sql,
			$this->escape($lat_s), $this->escape($long_w), $this->escape($lat_n),
			$this->escape($long_e), $this->escape($cid_stmt),
			$this->escape($sort), $this->escape($sort_order),
			$this->escape($results_per_page), $this->escape($offset)
		);
		return $this->getMapObjectsFromSQL($sql);
	}


	public final function mapHasSiblingWithSameCollectionAndPtr(Map $m) {
		$sql = sprintf(Query::MAP_COL_PTR_EXISTS_SQL,
			$this->escape($m->getCollectionID()), $this->escape($m->getPtr())
		);
		$result = $this->read($sql);
		return ($result[0]['count'] > 0) ? true : false;
	}


	public final function mapHasSiblingWithSameID(Map $m) {
		$sql = sprintf(Query::MAP_ID_EXISTS_SQL, $this->escape($m->getID()));
		$result = $this->read($sql);
		return ($result[0]['count'] > 0) ? true : false;
	}


	public final function deleteMap(Map $m) {
		$sql = sprintf(Query::DELETE_MAP_SQL, $this->escape($m->getID()));
		return $this->write($sql);
	}


	public final function saveMap(Map $m) {
		if (!$this->mapIsInDataStore($m)) { // insert
			$sql = sprintf(Query::ADD_MAP_SQL,
				$this->escape($m->getCollectionID()), $this->escape($m->getPtr()),
				$this->escape($m->getLatN()), $this->escape($m->getLatS()),
				$this->escape($m->getLongE()), $this->escape($m->getLongW())
			);
		}
		else { // update
			$sql = sprintf(Query::UPDATE_MAP_SQL,
				$this->escape($m->getCollectionID()), $this->escape($m->getPtr()),
				$this->escape($m->getLatN()), $this->escape($m->getLatS()),
				$this->escape($m->getLongE()), $this->escape($m->getLongW()),
				$this->escape($m->getID())
			);
		}
		return $this->write($sql);
	}


	private function getMapObjectsFromSQL($sql) {
		$maps = array();
		foreach ($this->read($sql) as $r) {
			$map = new Map();
			$map->setID($r['id']);
			$map->setCollectionID($r['collection_id']);
			$map->setPtr($r['ptr']);
			$map->setLatN($r['lat_n']);
			$map->setLatS($r['lat_s']);
			$map->setLongE($r['long_e']);
			$map->setLongW($r['long_w']);
			$maps[] = $map;
		}
		return $maps;
	}


	public final function loadMapProperties(Map $m) {
		$sql = sprintf(Query::GET_MAP_PROPERTIES_SQL,
			$this->escape($m->getID())
		);
		$result = $this->read($sql);
		$m->setPtr($result[0]['ptr']);
		$m->setCollectionID($result[0]['collection_id']);
		$m->setLatN($result[0]['lat_n']);
		$m->setLatS($result[0]['lat_s']);
		$m->setLongE($result[0]['long_e']);
		$m->setLongW($result[0]['long_w']);
	}


	public final function getNumMaps() {
		$result = $this->read(Query::ALL_MAPS_COUNT_SQL);
		return $result[0]['count'];
	}


	public final function getNumMapsOutside($lat_n, $lat_s, $long_e, $long_w,
	array $collection_ids) {
		$cid_stmt = (count($collection_ids) > 0)
			? 'AND collection_id IN(' . implode(', ', $collection_ids) . ')'
			: null;
		$sql = sprintf(Query::NUM_MAPS_OUTSIDE_SQL,
			$this->escape($lat_n), $this->escape($lat_s),
			$this->escape($long_e), $this->escape($long_w),
			$this->escape($cid_stmt)
		);
		$result = $this->read($sql);
		return $result[0]['count'];
	}


	public final function getNumMapsPartiallyWithin($lat_n, $lat_s, $long_e,
	$long_w, array $collection_ids) {
		$cid_stmt = (count($collection_ids) > 0)
			? 'AND collection_id IN(' . implode(', ', $collection_ids) . ')'
			: null;
		$sql = sprintf(Query::NUM_MAPS_PARTIALLY_WITHIN_SQL,
			$this->escape($long_w), $this->escape($long_e),
			$this->escape($long_e), $this->escape($long_w),
			$this->escape($lat_s), $this->escape($lat_n),
			$this->escape($lat_n), $this->escape($lat_s),
			$this->escape($cid_stmt)
		);
		$result = RDBMSDataStore::getInstance()->read($sql);
		return $result[0]['count'];
	}


	public final function getNumMapsWithin($lat_n, $lat_s, $long_e, $long_w,
	array $collection_ids) {
		$cid_stmt = (count($collection_ids) > 0)
			? 'AND collection_id IN(' . implode(', ', $collection_ids) . ')'
			: null;
		$sql = sprintf(Query::NUM_MAPS_WITHIN_SQL,
			$this->escape($lat_s), $this->escape($long_w),
			$this->escape($lat_n), $this->escape($long_e),
			$this->escape($cid_stmt)
		);
		$result = RDBMSDataStore::getInstance()->read($sql);
		return $result[0]['count'];
	}

} // Database

?>
